
ALTER  proc sp_create_update_ws_lot (

@part_number varchar(50), 
@lot_number varchar(50), 
@qty_in int,
@qty_out int,
@date_in datetime,
@date_out datetime,
@inventory_type varchar(50),
@vendor_name varchar(50))

as
begin

	declare @parent_prd_id int
	declare @base_part_number varchar(50)
	select @parent_prd_id = P_Parent_Product_ID from product where part_number like @part_number + '%'
	select @base_part_number = part_number from product where product_id = @parent_prd_id

	declare @process_step_id int
	declare @location_id int
	declare @int int
	declare @normal_cost float
	declare @lot_id int
	declare @this_lot_status varchar(50)

	select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @base_part_number and PS_Type_Name like @inventory_type and product.ProcessStep_ID=Process_Step.ProcessStep_ID 
	select @location_id = Location_ID from Location where L_Type = @inventory_type and L_Vendor_Name = @vendor_name
	select @this_lot_status = Lot_Current_Status, @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
	print 'process step id - ' + convert (varchar, @process_Step_id)

	if @lot_id is null -- Check if the lot exists
		begin -- Create Lot
			select @normal_cost = PSLT_Cost_Normal from Process_Step_Location_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id
			insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Lot_Cost, Lot_Current_Status, Timestamp) values (@process_step_id, @location_id, @lot_number, (@normal_cost * @qty_in), 'Running', CURRENT_TIMESTAMP)
			select @lot_id = @@IDENTITY
		end

	else if @this_lot_status = 'finished'   -- Possibly, running Adaptor again for this lot
	begin				        -- Skip processing
		update Lot_Table set Lot_Current_Status = @this_lot_status where Lot_ID = @lot_id
		return
	end

	-- insert/update Inventory Lot
	declare @inv_lot_id int
	select @inv_lot_id = Lot_ID from Inventory_Lot where lot_id = @lot_id
	if @inv_lot_id is null
		begin
		Insert into Inventory_Lot (Lot_ID, IL_Qty_In, IL_Qty_Out, IL_Date_In, IL_Date_Out) values (@lot_id, @qty_in, @qty_out, @date_in, @date_out)
		end
	else
		begin
		update Inventory_Lot set IL_Date_Out = @date_out, IL_Qty_Out = @inv_lot_id where Lot_ID = @inv_lot_id
		update Lot_Table set Lot_Current_Status = 'finished' where Lot_ID = @lot_id
		end

end

